package com.hotel.core.utils;

import org.apache.poi.hssf.usermodel.*;

import java.beans.IntrospectionException;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class FileUtil {


    /**
     * 导出Excel
     *
     * @param sheetName   sheet页名称
     * @param columnNames 列的名称数组
     * @param contentList 内容
     * @return
     * @throws java.beans.IntrospectionException
     * @throws IllegalAccessException
     * @throws java.lang.reflect.InvocationTargetException
     */
    public static HSSFWorkbook exportExcel(String sheetName, String[] columnNames, List<List<String>> contentList) throws IntrospectionException, IllegalAccessException, InvocationTargetException {
        HSSFWorkbook book = new HSSFWorkbook();
        HSSFSheet sheet = book.createSheet(sheetName);
        HSSFRow row = null;
        List<String> cellList = null;
        //设置内容
        for (int i = 0; i < contentList.size(); i++) {
            row = sheet.createRow(i + 1);
            cellList = contentList.get(i);
            for (int cIndex = 0; cIndex < cellList.size(); cIndex++) {
                row.createCell(cIndex).setCellValue(cellList.get(cIndex));
            }
        }

        //设置columnName名称，放后面原因:支持设置自动适应宽度
        row = sheet.createRow(0);
        for (int i = 0; i < columnNames.length; i++) {
            row.createCell(i).setCellValue(columnNames[i]);
            sheet.autoSizeColumn(i, true);

        }
        return book;
    }

    /**
     * 导入Excel
     *
     * @param book Excel文件
     * @return
     * @throws java.beans.IntrospectionException
     * @throws IllegalAccessException
     * @throws java.lang.reflect.InvocationTargetException
     */
    public static List<List<String>> importExcel(HSSFWorkbook book) throws IntrospectionException, IllegalAccessException, InvocationTargetException {
        if (book != null) {
            List<List<String>> contentList = new ArrayList<List<String>>();
            //得到第一页
            HSSFSheet sheet = book.getSheetAt(0);
            //获取总行数
            int rowNum = sheet.getPhysicalNumberOfRows();
            List<String> rowList = null;
            int columnNum = 0;
            if (sheet.getRow(0) != null)//取出title的count
                columnNum = sheet.getRow(0).getPhysicalNumberOfCells();

            for (int rowIndex = 0; rowIndex < rowNum; rowIndex++) {
                if (rowIndex == 0) //过掉title
                    continue;
                rowList = new ArrayList<String>();
                HSSFRow _row = sheet.getRow(rowIndex);
                for (int columnIndex = 0; columnIndex < columnNum; columnIndex++) {
                    rowList.add(getCellFormatValue(_row.getCell(columnIndex)));
                }
                contentList.add(rowList);
            }
            return contentList;
        }
        return null;
    }

    /**
     * 根据HSSFCell类型设置数据
     *
     * @param cell
     * @return
     */
    protected static String getCellFormatValue(HSSFCell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式

                        //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                        //cellvalue = cell.getDateCellValue().toLocaleString();

                        //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                        Date date = cell.getDateCellValue();
                        cellvalue = SysDate.getDate(date);

                    } else {
                        // 取得当前Cell的数值
                        if (cell.getNumericCellValue() % 1 == 0) {
                            DecimalFormat format = new DecimalFormat("#");
                            cellvalue = String.valueOf(format.format(cell.getNumericCellValue()));
                        } else {
                            cellvalue = String.valueOf(cell.getNumericCellValue());
                        }
                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                // 默认的Cell值
                default:
                    cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }
}
